
-- network_sql: in order origin, destination, capacity, lowerbound, cost, value
-- note: lowerbound is unused here  and must be zero
CREATE OR REPLACE FUNCTION mincost_ns(
    network_sql TEXT,
    source TEXT, 
    target TEXT, 
    requiredFlow BIGINT,
    OUT origin TEXT,
    OUT destination TEXT,
    OUT capacity BIGINT,
    OUT lowerbound BIGINT,
    OUT cost FLOAT8,
    OUT value BIGINT,
    OUT vname TEXT
    )
RETURNS SETOF RECORD AS
'$libdir/pgnetworkflow', 'mincost_ns_c'
LANGUAGE c VOLATILE;


-- network_sql output in order: origin, destination, capacity, lowerbound, cost, value
CREATE OR REPLACE FUNCTION mincost_circulation(
    network_sql TEXT
    )
  RETURNS TABLE(origin TEXT, destination TEXT, capacity BIGINT, lowerbound BIGINT, cost FLOAT8, value BIGINT, vname TEXT) LANGUAGE plpgsql AS
  $func$
  DECLARE total_lowerbound BIGINT;
BEGIN
  -- transform edges w/ lower bounds to two opposite edges, one with the capacity and the other with the lowerbound and negated cost.
  -- then add isolated psuodo s, t nodes
  -- this is assuming that the mincost solver can handle negative costs
 
  --0. normalize input and compute total sum of lower bounds
  EXECUTE format(
    $fmt$
    DROP TABLE IF EXISTS _temp_input;
    CREATE TABLE _temp_input AS --todo TEMP
    SELECT origin, destination, capacity, lowerbound, cost, value, vname FROM (%1$s) t
    ;
    $fmt$, TRIM(TRAILING ';' FROM TRIM(network_sql)) );
  PERFORM set_default_flow_vname('_temp_input');
  
  EXECUTE format('SELECT sum(lowerbound) total_lowerbound FROM _temp_input') INTO total_lowerbound;
 
  --1. create temp table containing the mincost network
  EXECUTE format(
    $fmt$
    DROP TABLE IF EXISTS _temp_mcc;
    CREATE TABLE _temp_mcc AS --todo TEMP
    SELECT origin, destination, (capacity - lowerbound)::BIGINT capacity, 0::BIGINT lowerbound, cost, value, vname FROM _temp_input
    UNION
    SELECT origin, 'b_t',  sum(lowerbound)::BIGINT capacity, 0::BIGINT lowerbound, 0, 0, '' vname  FROM _temp_input GROUP BY origin
    UNION
    SELECT 'b_s', destination,  sum(lowerbound)::BIGINT capacity, 0::BIGINT lowerbound, 0, 0, '' vname  FROM _temp_input GROUP BY destination 
--    UNION
--    SELECT 'b_t', 'b_s', $1 capacity, -1, 0, '' vname
    ;
    TABLE _temp_mcc;
    $fmt$);
  PERFORM set_default_flow_vname('_temp_mcc');
  
  --RAISE NOTICE 'mincost_circulation, ok,(_temp_mcc) total_lowerbound = %', total_lowerbound ; return; --debug
  
  
  --2. call mincost and save result to another temp table
  EXECUTE format(
    $fmt$
    DROP TABLE IF EXISTS _temp_mcmf; 
    CREATE TABLE _temp_mcmf AS --todo TEMP
    SELECT origin, destination, capacity, lowerbound, cost, value, vname FROM mincost_ns('TABLE _temp_mcc', 'b_s', 'b_t', %1$s);
    $fmt$, total_lowerbound);
  --RAISE NOTICE 'mincost_circulation, ok2'; return; --debug
  
  --3. return transformed result set
  RETURN QUERY EXECUTE format(
    $fmt$
--    SELECT m.origin, m.destination, m.capacity + i.lowerbound capacity, i.lowerbound lowerbound, m.cost, m.value, m.vname
--    SELECT m.origin, m.destination, m.capacity capacity, i.lowerbound lowerbound, m.cost, m.value, m.vname
    SELECT m.origin, m.destination, m.capacity + i.lowerbound capacity, i.lowerbound lowerbound, m.cost, m.value + i.lowerbound value, m.vname
    FROM _temp_mcmf m INNER JOIN _temp_input i ON m.vname = i.vname
    UNION ALL
    SELECT m.origin, m.destination, m.capacity, m.lowerbound, m.cost, m.value, m.vname
    FROM _temp_mcmf m WHERE m.vname ilike '_meta_%%'
    ;
    $fmt$);
  
END $func$;


-- get the out flow at a given node
CREATE OR REPLACE FUNCTION outflow_at(network_tbl regclass, node TEXT)
  RETURNS BIGINT AS
  $func$
  DECLARE outflow BIGINT;
BEGIN
  EXECUTE format($fmt$ SELECT sum(value) FROM %1$s WHERE origin = %2$L; $fmt$,network_tbl, node) INTO outflow;
  RETURN outflow;  
END $func$
LANGUAGE plpgsql STRICT;


CREATE OR REPLACE FUNCTION set_default_flow_vname(_network_tbl regclass)
  RETURNS boolean AS
$func$
BEGIN
  EXECUTE format($f$ UPDATE %1$s SET vname = COALESCE(NULLIF(TRIM(vname), ''), '('|| origin || ',' || destination || ')')
                 $f$, _network_tbl); 
  RETURN true;
END $func$ LANGUAGE plpgsql;
